﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Text;
using System.Data;

namespace TireBias.Models
{
    public class ReportModel
    {
        public class Report_DS
        {
            public string _biasno { get; set; }
            public string _plandt { get; set; }
            public string _itemno { get; set; }
            public string _topcrdcd { get; set; }
            public string _sizes { get; set; }
            public string _forming { get; set; }
            public string _P1 { get; set; }
            public string _P2 { get; set; }
            public string _P3 { get; set; }
            public string _P4 { get; set; }
            public string _Text { get; set; }
            public string _plansts { get; set; }
            public string _Uptemp { get; set; }
            public string _Uptdat { get; set; }
            public string _actflag { get; set; }
            public string _actdt { get; set; }
            public string _empname { get; set; }
            public string _shift { get; set; }
            public string _finflag { get; set; }
            public string _findat { get; set; }
            public string _Tcrdlot { get; set; }
            public string _toproll { get; set; }
            public string _topexp { get; set; }
            public string _toplen { get; set; }
            public string _topauthemp { get; set; }
            public string _topreatxt { get; set; }
            public string _topshtxt { get; set; }
            public string _Skpflag { get; set; }
            public string _skpdt { get; set; }
            public string _skpemp { get; set; }
            public string _skpshtxt { get; set; }
            public string _smpemp { get; set; }
            public string _smpshtxt { get; set; }

            public string _SUB_biasno { get; set; }
            public string _SUB_plandt { get; set; }
            public string _SUB_itemno { get; set; }
            public string _SUB_sitemno { get; set; }
            public string _SUB_Ply1 { get; set; }
            public string _SUB_Ply2 { get; set; }
            public string _SUB_Rundt { get; set; }
            public string _SUB_finflag { get; set; }
            public string _SUB_findat { get; set; }
            public string _SUB_empname { get; set; }
            public string _SUB_shift { get; set; }
            public string _SUB_sauthemp { get; set; }
            public string _SUB_sreatxt { get; set; }
            public string _SUB_sshtxt { get; set; }
            public string _SUB_angPLY1 { get; set; }
            public string _SUB_angPLY2 { get; set; }

            public string _PLY_biasno { get; set; }
            public string _PLY_plandt { get; set; }
            public string _PLY_itemno { get; set; }
            public string _PLY_sitemno { get; set; }
            public string _PLY_side { get; set; }
            public string _PLY_rollno { get; set; }
            public string _PLY_cartno { get; set; }
            public string _PLY_plylot { get; set; }
            public string _PLY_plyexp { get; set; }
            public string _PLY_plylen { get; set; }
            public string _PLY_Plyan1 { get; set; }
            public string _PLY_Plyan2 { get; set; }
            public string _PLY_Plyan3 { get; set; }
            public string _PLY_Plywd1 { get; set; }
            public string _PLY_Plywd2 { get; set; }
            public string _PLY_Plywd3 { get; set; }
            public string _PLY_Plyjn1 { get; set; }
            public string _PLY_Plyjn2 { get; set; }
            public string _PLY_Plyjn3 { get; set; }
            public string _PLY_empname { get; set; }
            public string _PLY_shift { get; set; }
            public string _PLY_update { get; set; }
            public string _NFINEmp { get; set; }
            public string _NFINshtxt { get; set; }

            public string _Side { get; set; }
            public string _Rollno { get; set; }
            public string _Cartno { get; set; }
            public string _PlyLot { get; set; }
            public string _PlyExp { get; set; }
            public string _PlyLen { get; set; }
            public string _Plyan1 { get; set; }
            public string _Plyan2 { get; set; }
            public string _Plyan3 { get; set; }
            public string _Plywd1 { get; set; }
            public string _Plywd2 { get; set; }
            public string _Plywd3 { get; set; }
            public string _Plyjn1 { get; set; }
            public string _Plyjn2 { get; set; }
            public string _Plyjn3 { get; set; }
            public string _cEmpname { get; set; }
            public string _Update { get; set; }
        }

        public List<Report_DS> GET_REPORT(string top = "",string dFrom ="",string dTo ="" ,string biasno="")
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            string where = " where ";
             str.AppendLine("select a.biasno,a.plandt,a.itemno,");
             str.AppendLine("a.topcrdcd,a.sizes,a.forming,");
             str.AppendLine("a.P1,a.P2,a.P3,a.P4,");
             str.AppendLine("a.[Text],a.plansts,a.Uptemp,");
             str.AppendLine("a.Uptdat,a.actflag,a.actdt,");
             str.AppendLine("a.empname,a.shift,a.finflag,");
             str.AppendLine("a.findat,a.Tcrdlot,a.toproll,");
             str.AppendLine("a.topexp,a.toplen,a.topauthemp,");
             str.AppendLine("a.topreatxt,a.topshtxt,a.Skpflag,");
             str.AppendLine("a.skpdt,a.skpemp,a.skpshtxt,");
             str.AppendLine("a.smpemp,a.smpshtxt,a.NFINEmp,a.NFINshtxt,");
             str.AppendLine("b.sitemno,b.Ply1,b.Ply2,b.Rundt,b.finflag bfinflag,b.findat bfindat,");
             str.AppendLine("b.empname bempname,b.shift bshift,b.sauthemp,b.sreatxt,b.sshtxt,b.angPLY1,b.angPLY2,");
             str.AppendLine(" case when c.side='L' then b.ply1 when c.side='R' then b.ply2 else c.side end side");
             str.AppendLine(",c.rollno,c.cartno,c.plylot,c.plyexp,c.plylen,c.plyan1,c.plyan2,c.plyan3,");
             str.AppendLine("c.plywd1,c.plywd2,c.plywd3,c.plyjn1,c.plyjn2,c.plyjn3,c.empname,c.[update] empUpd from ");
             str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN a ");
             str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASSUBPLAN b ");
             str.AppendLine("on a.biasno=b.biasno and a.plandt=b.plandt and a.itemno=b.itemno ");
             str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL c ");
             str.AppendLine(" on a.biasno=c.biasno and a.plandt=c.plandt and a.itemno=c.itemno and b.sitemno=c.sitemno ");

             if (StrUtil.IsNotEmpty(dFrom) && StrUtil.IsNotEmpty(dTo))
             {
                 str.AppendLine(where + " a.plandt between convert(datetime,@dF,120) and convert(datetime,@dT,120) ");
                 where = "and";
             }
             else if (StrUtil.IsNotEmpty(dFrom))
             {
                 str.AppendLine(where + " a.plandt = convert(datetime,@dF,120)  ");
                 where = "and";
             }
             if (StrUtil.IsNotEmpty(biasno))
             {
                 str.AppendLine(where + " a.biasno =@biasno ");
                   where = "and";
             }


            SqlCommand cmd = new SqlCommand(str.ToString() + " ORDER BY  a.biasno,a.PLANDT,convert(int,a.ITEMNO),convert(int,b.sitemno) ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            if (StrUtil.IsNotEmpty(dFrom)) cmd.Parameters.Add("@dF", SqlDbType.VarChar).Value = DateUtil.ToInternalDateTime(dFrom, WinNemoLib.DateUtil.eLanguage.EN);
            if (StrUtil.IsNotEmpty(dTo)) cmd.Parameters.Add("@dT", SqlDbType.VarChar).Value = DateUtil.ToInternalDateTime(dTo, WinNemoLib.DateUtil.eLanguage.EN);

            if (StrUtil.IsNotEmpty(biasno))
            {
                cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;
            }

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            List<Report_DS> list = new List<Report_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                Report_DS model = new Report_DS();

                model._biasno = items["biasno"].ToString();
                model._itemno = items["itemno"].ToString();
                model._topcrdcd = items["topcrdcd"].ToString();
                model._plansts = items["Plansts"].ToString();
                model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
                model._itemno = items["itemno"].ToString();
                model._topcrdcd = items["topcrdcd"].ToString();
                model._sizes = items["sizes"].ToString();
                model._forming = items["forming"].ToString();
                model._P1 =  items["P1"].ToString();
                model._P2 =items["P2"].ToString();
                model._P3 =items["P3"].ToString();
                model._P4 =   items["P4"].ToString();
                model._Text =items["Text"].ToString();
                model._plansts =  items["plansts"].ToString();
                model._Uptemp =   items["Uptemp"].ToString();
                model._Uptdat = DateUtil.ToDisplayDateTime(items["Uptdat"], DateUtil.eLanguage.EN);
                model._actflag = items["actflag"].ToString();
                model._actdt = items["actdt"].ToString();
                model._empname =  items["empname"].ToString();
                model._shift = items["shift"].ToString();
                model._finflag =  items["finflag"].ToString();
                model._findat =    DateUtil.ToDisplayDateTime(items["findat"], DateUtil.eLanguage.EN);
                model._Tcrdlot =  items["Tcrdlot"].ToString();
                model._toproll =items["toproll"].ToString();
                model._topexp = items["topexp"].ToString();
                model._toplen =   items["toplen"].ToString();
                model._topauthemp =   items["topauthemp"].ToString();
                model._topreatxt =  items["topreatxt"].ToString();
                model._topshtxt =  items["topshtxt"].ToString();
                model._Skpflag = items["Skpflag"].ToString();
                model._skpemp = items["skpemp"].ToString();
                model._skpdt = DateUtil.ToDisplayDateTime(items["skpdt"], DateUtil.eLanguage.EN);
                model._skpshtxt = items["skpshtxt"].ToString();
                model._smpemp = items["smpemp"].ToString();
                model._smpshtxt = items["smpshtxt"].ToString();

                model._SUB_sitemno=items["sitemno"].ToString();
                model._SUB_Ply1=items["Ply1"].ToString();
                model._SUB_Ply2=items["Ply2"].ToString();
                model._SUB_Rundt= DateUtil.ToDisplayDateTime(items["Rundt"], DateUtil.eLanguage.EN);
                model._SUB_finflag=items["finflag"].ToString();
                model._SUB_findat=DateUtil.ToDisplayDateTime(items["bfindat"], DateUtil.eLanguage.EN);
                model._SUB_empname=items["bempname"].ToString();
                model._SUB_shift=items["bshift"].ToString();
                model._SUB_sauthemp=items["sauthemp"].ToString();
                model._SUB_sreatxt=items["sreatxt"].ToString();
                model._SUB_sshtxt=items["sshtxt"].ToString();
                model._SUB_angPLY1=items["angPLY1"].ToString();
                model._SUB_angPLY2=items["angPLY2"].ToString();
                model._NFINEmp = items["NFINEmp"].ToString();
                model._NFINshtxt = items["NFINshtxt"].ToString();

                model._Side = items["side"].ToString();
                model._Rollno = items["rollno"].ToString();
                model._Cartno = items["cartno"].ToString();
                model._PlyLot = items["plylot"].ToString();
                model._PlyExp = DateUtil.ToDisplayDate(items["plyexp"], DateUtil.eLanguage.EN);
                model._PlyLen = items["plylen"].ToString();
                model._Plyan1 = items["plyan1"].ToString();
                model._Plyan2 = items["plyan2"].ToString();
                model._Plyan3 = items["plyan3"].ToString();
                model._Plywd1 = items["plywd1"].ToString();
                model._Plywd2 = items["plywd2"].ToString();
                model._Plywd3 = items["plywd3"].ToString();
                model._Plyjn1 = items["Plyjn1"].ToString();
                model._Plyjn2 = items["Plyjn2"].ToString();
                model._Plyjn3 = items["Plyjn3"].ToString();
                model._empname = items["empname"].ToString();
                
                model._Update = DateUtil.ToDisplayDateTime(items["empUpd"], DateUtil.eLanguage.EN);
                list.Add(model);

            }
            return list;
        }

        public DataTable SEL_Report(string biasno,string dtexp,string shift,string topcode,string lot,string roll)
        {


            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select  * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.Report where biasno=@code ");
            if (StrUtil.IsNotEmpty(dtexp))
            {
                str.AppendLine(" and [prd_date]=convert(datetime,@dt,120)");
            }
            if (StrUtil.IsNotEmpty(shift))
            {
                str.AppendLine(" and shift =" + shift);
            }
            if (StrUtil.IsNotEmpty(topcode))
            {
                str.AppendLine(" and topcrdcd =@topcode");
            }
            if (StrUtil.IsNotEmpty(lot))
            {
                str.AppendLine(" and tcrdlot =@tcrdlot");
            }
            if (StrUtil.IsNotEmpty(roll))
            {
                str.AppendLine(" and toproll =@roll");
            }


            SqlCommand cmd = new SqlCommand(str.ToString() + " order by prd_date,shift,[update] ", cnn);
            cmd.Connection.Open();

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = biasno;

            if (StrUtil.IsNotEmpty(dtexp))
            {
                cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDateTime(dtexp, WinNemoLib.DateUtil.eLanguage.EN); 
            }
            if (StrUtil.IsNotEmpty(topcode)) cmd.Parameters.Add("@topcode", SqlDbType.VarChar).Value = topcode;
            if (StrUtil.IsNotEmpty(lot)) cmd.Parameters.Add("@tcrdlot", SqlDbType.VarChar).Value = lot;
            if (StrUtil.IsNotEmpty(roll)) cmd.Parameters.Add("@roll", SqlDbType.VarChar).Value = roll;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;

            my.Fill(ds);
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return ds.Tables[0];
            }

            return ds.Tables[0];


        }

        public DataTable SEL_Topping( string topcode, string lot, string roll)
        {


            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select  * from " + Nemo.GetINI("Dbase", "TOPPING") + ".dbo.RptHeadNTail ");
            str.AppendLine(" where topcrdcd =@topcode");
            str.AppendLine(" and tcrdlot =@tcrdlot");    
            str.AppendLine(" and ROLLNO =@roll");
           


            SqlCommand cmd = new SqlCommand(str.ToString() , cnn);
            cmd.Connection.Open();

            cmd.CommandType = CommandType.Text;
     
            if (StrUtil.IsNotEmpty(topcode)) cmd.Parameters.Add("@topcode", SqlDbType.VarChar).Value = topcode;
            if (StrUtil.IsNotEmpty(lot)) cmd.Parameters.Add("@tcrdlot", SqlDbType.VarChar).Value = lot;
            if (StrUtil.IsNotEmpty(roll)) cmd.Parameters.Add("@roll", SqlDbType.VarChar).Value = roll;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;

            my.Fill(ds);
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return ds.Tables[0];
            }

            return ds.Tables[0];


        }


        public DataTable SEL_MATCHANGE(string biasno = "", string plandt = "", string itemno = "", string sitemno = "", string rollno = "", string side = "", string dFrom="",string dTo="")
        {


            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select tcrdlot toplot, c.topcrdcd,c.biasno,c.plandt,c.itemno,c.sitemno ,rollno,toproll,toplen,cartno,b.shift,a.sizes oldsize ,c.newsizes sizes,c.newside side,c.newforming forming,newangle angle,newlen plylen,newwidth plymin,newlot plylot,newExp plyexp,newTape tapesheet,newRub topsheet");
            str.AppendLine(",c.modi_by empname,c.modi_date");
            str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN a ");
             str.AppendLine(" INNER JOIN " + Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL b on a.biasno=b.biasno ");
             str.AppendLine(" and a.plandt=b.plandt and a.itemno=b.itemno ");
             str.AppendLine(" INNER JOIN " + Nemo.GetINI("Dbase", "Base") + ".dbo.MatToMat c on a.biasno=c.biasno ");
             str.AppendLine(" and b.plandt=c.plandt and b.itemno=c.itemno ");
             str.AppendLine(" and b.sitemno=c.sitemno and b.side=c.side   ");

             if (StrUtil.IsNotEmpty(biasno))
             {
                 str.AppendLine(" and c.biasno =" + biasno);
             }

             if (StrUtil.IsNotEmpty(plandt))
             {
                 str.AppendLine(" and a.plandt=convert(datetime,@dt,120) ");
             }
             if (StrUtil.IsNotEmpty(itemno))
             {
                 str.AppendLine(" and a.itemno=@itemno");
             }
             if (StrUtil.IsNotEmpty(sitemno))
             {
                 str.AppendLine(" and b.sitemno=@sitemno");
             }
              if (StrUtil.IsNotEmpty(side))
             {
                 str.AppendLine(" and c.side=@side");
             }

              if (StrUtil.IsNotEmpty(rollno))
             {
                 str.AppendLine(" and rollno=@rollno");
             }
              if (StrUtil.IsNotEmpty(dFrom) && StrUtil.IsNotEmpty(dTo))
              {
                  str.AppendLine("and a.plandt between convert(datetime,@dF,120) and convert(datetime,@dT,120) ");
                  
              }
              else if (StrUtil.IsNotEmpty(dFrom))
              {
                  str.AppendLine("and a.plandt = convert(datetime,@dF,120)  ");               
              }
         
            SqlCommand cmd = new SqlCommand(str.ToString() + "", cnn);
            cmd.Connection.Open();

            cmd.CommandType = CommandType.Text;

            if (StrUtil.IsNotEmpty(plandt))
            {
                cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDateTime(plandt, WinNemoLib.DateUtil.eLanguage.EN);
            }

            if (StrUtil.IsNotEmpty(biasno))
            {
                cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno; 
            }

            if (StrUtil.IsNotEmpty(biasno))
            {
                cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;              
            }
            if (StrUtil.IsNotEmpty(sitemno))
            {
                cmd.Parameters.Add("@sitemno", SqlDbType.VarChar).Value = sitemno;
            }
            if (StrUtil.IsNotEmpty(side))
            {
                cmd.Parameters.Add("@side", SqlDbType.VarChar).Value = side;
            }
            if (StrUtil.IsNotEmpty(rollno))
            {
                cmd.Parameters.Add("@rollno", SqlDbType.VarChar).Value = rollno;
            }
            if (StrUtil.IsNotEmpty(dFrom)) cmd.Parameters.Add("@dF", SqlDbType.VarChar).Value = DateUtil.ToInternalDateTime(dFrom, WinNemoLib.DateUtil.eLanguage.EN);
            if (StrUtil.IsNotEmpty(dTo)) cmd.Parameters.Add("@dT", SqlDbType.VarChar).Value = DateUtil.ToInternalDateTime(dTo, WinNemoLib.DateUtil.eLanguage.EN);

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;

            my.Fill(ds);
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return ds.Tables[0];
            }

            return ds.Tables[0];


        }
    }
}